var mysql = require('mysql');
var config = require('./config.js');

var connection = mysql.createConnection({//创建与数据库的连接
    host: config.db.host,
    user: config.db.user,
    password: config.db.password,
    database: config.db.database,
    dateStrings: true,
    multipleStatements: true
});
connection.connect(function(err){//连接数据库
    if(err){
        console.log(err);
    }else{
        console.log('连接数据库成功');
    }
});
//进行注册用户账号时调佣，获取用户输入的信息保存数据库中生成账号
module.exports.charu = function(obj,callback){
    var username = obj.username;
    var password = obj.password;
    var gender = obj.gender;
    var lables = obj.lables;

    var sql = 'insert into admin(username, password, gender, lables)values(?, ?, ?, ?);';
    connection.query(sql, [username, password, gender, lables],function(err, result){
        if(err) {
            console.log(err);
        }else{
            callback();
        }
    });
}
//用户登录时调用作用为在数据库中检查是否存在该用户
module.exports.checkAdmin = function (username, pwd, callback) {
    var sql = 'select * from admin where username = ? and password = ?';
    connection.query(sql, [username, pwd], function(err,result){
        if(err){
            console.log(err);
        }else{
            callback(result);
        }
    });
}
module.exports.getAll = function (callback) {
    var sql = 'select * from post ORDER BY id DESC';//按照id进行逆序输出表内容。
    connection.query(sql, function (err, result) {
        if(err) {
            console.log(err);
        }else{
            callback(result);
        }
    });
}
module.exports.getQ = function (filedName, value, callback) {//遍历评论
    var sql = 'select * from reply where ?? = ?';
    connection.query(sql, [filedName, value], function(err,result){
        if(err){
            console.log(err);
        }else{
            callback(result);
        }
    });
}
module.exports.getByField = function (filedName, value, callback) {
    //该函数在handler中经由多个函数调用，主要用于文章的搜索遍历，以及修改
    var sql = 'select * from post where ?? = ?';
    connection.query(sql, [filedName, value], function(err,result){
        if(err){
            console.log(err);
        }else{
            callback(result);
        }
    });
}

//插入函数，用语发表文章时调用将文章保存至数据库中
module.exports.insert = function(obj,callback){
    var username = obj.username;
    var time = obj.time;
    var title = obj.title;
    var conments = obj.conments;

    var sql = 'insert into post(username, time, title, conments)values(?, ?, ?, ?);';
    connection.query(sql, [username, time, title, conments],function(err, result){
        if(err) {
            console.log(err);
        }else{
            callback();
        }
    });
}
//将用户对文章的留言进行保存到数据库中
module.exports.leave = function(obj,callback){
    var username = obj.username;
    var time = obj.time;
    var tiezi = obj.tiezi;
    var title= obj.title;

    var sql = 'insert into reply(username, time, tiezi,title)values(?, ?, ?, ?);';
    connection.query(sql, [username, time,tiezi,title],function(err, result){
        if(err) {
            console.log(err);
        }else{
            callback();
        }
    });
}


//在获取到用户所发文章时调用，通过id进行搜索相应违章并删除
module.exports.deleById = function (id, callback) {
    var sql = 'delete from post where id = ?';
    connection.query(sql, [id], function(err,result){
        if(err){
            console.log(err);
        }else{
            callback();
        }
    });
}
//涉及更新操作时进行调用
module.exports.updateById = function (obj, callback) {
    var sql = 'update post set username = ?, time = ?, title = ?,  conments = ? where id = ?';

    connection.query(sql, [obj.username, obj.time, obj.title, obj.conments, obj.id], function(err, result){
        if(err){
            console.log(err);
        }else{
            callback();
        }
    });
}
